﻿using Dapper;
using StudentWeb.IDAL;
using StudentWeb.Model;
using StudentWeb.Common;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace StudentWeb.DAL
{
	/// <summary>
	/// TravelNotes的数据操作类
	/// </summary>
	public partial class TravelNotesDataDAO : ITravelNotesDataDAO
	{
        #region 分页列表
        public PagedList<dynamic> PageNotesList(int pageIndex, int pageSize, string
        whereClause = null)
        {
            pageIndex--;
            string where = $@"where TravelNotesData.NoteID > 0 {whereClause}";
            string orderBy = "TravelNotesData.NoteID DESC";
            var startItem = pageIndex * pageSize + 1;
            var endItem = (pageIndex + 1) * pageSize;
            var selFields = $@"NoteID, TouristID, Title,Content,PublishDate,Views,Likes";
            var tabSql = $" TravelNotesData {where}";
            var sql = $@"SELECT COUNT(TravelNotesData.NoteID) FROM {tabSql}
SELECT *
FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS RowNum, {selFields}
FROM {tabSql}) AS result
WHERE RowNum >= {startItem}AND RowNum <= {endItem}
ORDER BY RowNum";
            using (var conn = ConnectionFactory.CreateConnection())
            {
                conn.Open();
                int count = 0;
                var list = new List<dynamic>();
                using (var q = conn.QueryMultiple(sql))
                {
                    count = q.Read<int>().Single();
                    list = q.Read<dynamic>().ToList();
                }
                conn.Close();
                var pagedList = new PagedList<dynamic>(list, pageIndex, pageSize,
                count);
                return pagedList;
            }
        }
        #endregion
    }
}

